MySQL Select
MySQL SELECT
MYSQL SELECT DISTINCT
MYSQL Where Clause
MYSQL AND, OR, NOT
MySQL ORDER BY
MySQL LIMIT Clause
MySQL MIN() and MAX()
MySQL COUNT(), AVG() and SUM()
MYSQL IN and NOT IN Operators
MySQL BETWEEN Operator
MySQL LIKE Operator
MySQL GROUP BY Statement
MySQL HAVING Clause
MySQL EXISTS
MySQL INSERT INTO SELECT Statement
MySQL CASE Statement
MySQL ANY and ALL Operators
MySQL NULL Functions
MySQL AUTO INCREMENT
MySQL Subquery

MySQL Joins
MYSQL Inner Join
MYSQL LEFT JOIN
MYSQL RIGHT JOIN
MYSQL CROSS JOIN
MYSQL SELF JOIN

MySQL Database & Tables
MySQL CREATE and DROP DATABASE
MySQL CREATE and DROP TABLE Statement
MySQL ALTER TABLE Statement

MySQL Insert, Update & Delete
MySQL INSERT
MySQL UPDATE
MySQL DELETE

MySQL Constraints
MySQL NOT NULL Constraint
MySQL UNIQUE Constraint
MySQL PRIMARY KEY Constraint
MySQL FOREIGN KEY Constraint
MySQL CHECK Constraint
MySQL DEFAULT Constraint
MySQL CREATE INDEX Statement

MySQL Additional Topics
MYSQL VIEWS
MySQL Stored Procedures

MySQL FOREIGN KEY Constraint

A FOREIGN KEY constraint is used to associate a table???s field, to the PRIMARY KEY in another table.

The table with the FOREIGN KEY is the child table, and the table with the PRIMARY KEY is called the parent table.
Consider the following 2 tables

Customers Table

cust_id	name	description	age
101	Peter	Engineer	32
102	Joseph	Developer	30
103	John	Leader	28
104	Stephen	Scientist	45
105	Suzi	Carpenter	26
106	Bob	Actor	25

Orders Table

order_id	cust_id	prod_name	order_date	amount
1	101	Laptop	2022-01-10	45000
2	103	Desktop	2022-02-12	35000
3	106	Iphone	2022-02-15	65000
4	104	Mobile	2022-03-05	12000

In the above 2 tables, cust_id column of Customers table is the primary key column.
In the Orders table we have a column cust_id which is the foreign key that refers to the cust_id field of the Customers table.

FOREIGN KEY on CREATE TABLE

Below is the query to create a table Orders with foreign key cust_id.

CREATE TABLE Orders (
  order_id int(11) NOT NULL,
  cust_id int(11) DEFAULT NULL,
  prod_name varchar(45) DEFAULT NULL,
  order_date date DEFAULT NULL,
  amount decimal(10,0) DEFAULT NULL
  FOREIGN  KEY (cust_id) REFERENCES Customers(cust_id)
)

FOREIGN KEY on Alter Table

It is possible to add a Foreign Key to a table even after creation of a table.
Below is the query to add a new Foreign Key to the Orders table.

ALTER  TABLE Orders  
ADD  FOREIGN  KEY (cust_id) REFERENCES Customers(cust_id);

Its possible to name a FOREIGN KEY constraint, This will help to refer the FOREIGN KEY with a name so that we can drop the FOREIGN KEY with its name.

ALTER  TABLE Orders
ADD CONSTRAINT FK_CustomerOrder
ADD  FOREIGN  KEY (cust_id) REFERENCES Customers(cust_id);

DROP a FOREIGN KEY Constraint

To drop a field with its FOREIGN KEY constraint, use the following query.

ALTER  TABLE Orders
DROP FOREIGN KEY FK_CustomerOrder;